package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.Post;
import com.scuec.groupfive.entity.PostComment;
import com.scuec.groupfive.entity.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PostMapper {
    /**
     * 查询所有帖子及其关联数据
     */
    @Select("SELECT p.*, " +
            "(SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.post_id) AS comment_count, " +
            "(SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.post_id) AS like_count, " +
            "(SELECT COUNT(*) FROM post_favorites pf WHERE pf.post_id = p.post_id) AS favorite_count " +
            "FROM posts p ORDER BY p.created_at DESC")
    @Results({
            @Result(property = "postId", column = "post_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at"),
            @Result(property = "viewCount", column = "view_count"),
            @Result(property = "commentCount", column = "comment_count"),
            @Result(property = "likeCount", column = "like_count"),
            @Result(property = "favoriteCount", column = "favorite_count")
    })
    List<Post> getAllPostsWithStats();

    /**
     * 分页查询帖子评论及用户信息
     */
    @Select("SELECT " +
            "pc.comment_id, pc.post_id, pc.user_id, pc.content, pc.parent_id, pc.status, " +
            "pc.created_at AS comment_created, pc.updated_at AS comment_updated, " +
            "u.username, u.email, u.avatar, u.role " +
            "FROM post_comments pc " +
            "LEFT JOIN users u ON pc.user_id = u.user_id " +
            "WHERE pc.post_id = #{postId} AND pc.status = 1 " +
            "ORDER BY pc.created_at DESC " +
            "LIMIT #{offset}, #{size}")
    @Results({
            @Result(property = "commentId", column = "comment_id"),
            @Result(property = "postId", column = "post_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "content", column = "content"),
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "status", column = "status"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at"),
            @Result(property = "user.userId", column = "user_id"),
            @Result(property = "user.username", column = "username"),
            @Result(property = "user.email", column = "email"),
            @Result(property = "user.phone", column = "phone"),
            @Result(property = "user.role", column = "role"),
            @Result(property = "user.status", column = "status"),
            @Result(property = "user.createTime", column = "created_at"),
            @Result(property = "user.updateTime", column = "updated_at"),
            @Result(property = "user.lastLoginTime", column = "last_login_time"),
            @Result(property = "user.realName", column = "real_name"),
            @Result(property = "user.gender", column = "gender"),
            @Result(property = "user.avatar", column = "avatar"),
            @Result(property = "user.school", column = "school"),
            @Result(property = "user.targetSchool", column = "target_school"),
            @Result(property = "user.examYear", column = "exam_year")
    })
    List<PostComment> getPostCommentsWithUserInfo(
            @Param("postId") long postId,
            @Param("offset") int offset,
            @Param("size") int size);

    /**
     * 通过评论ID获取用户信息
     */
    @Select("SELECT pc.*, u.user_id, u.username, u.email, u.phone, u.role, u.status, " +
            "u.created_at, u.updated_at, u.last_login_time, u.avatar " +  // 只选择users表中的字段
            "FROM post_comments pc " +
            "LEFT JOIN users u ON pc.user_id = u.user_id " +  // 只保留users表的关联
            "WHERE pc.post_id = #{postId} AND pc.status = 1 " +
            "ORDER BY pc.created_at DESC " +
            "LIMIT #{offset}, #{size}")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "username", column = "username"),
            @Result(property = "email", column = "email"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "role", column = "role"),
            @Result(property = "status", column = "status"),
            @Result(property = "createTime", column = "created_at"),
            @Result(property = "updateTime", column = "updated_at"),
            @Result(property = "lastLoginTime", column = "last_login_time"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "avatar", column = "avatar"),
            @Result(property = "school", column = "school"),
            @Result(property = "targetSchool", column = "target_school"),
            @Result(property = "examYear", column = "exam_year")
    })
    UserInfo getUserByCommentId(@Param("commentId") String commentId);



}